﻿using System;
using System.Collections.Generic;
using Less.Security.DAL.Records;

namespace Less.Security.DAL
{
	/// <summary>
	/// Data Access Layer to work with Users Records
	/// </summary>
	public static class UsersDAL
	{
        /// <summary>
        /// <add key="TableName" value="Users"/>
        /// </summary>
        /// <returns></returns>
        public static string TableName
        {
            get { return Config.GetValue<string>("UsersDAL.TableName", "Users"); }
            set { Config.SetValue("UsersDAL.TableName", value); }
        }

        /// <summary>
        /// <add key="IdColumnName" value="Id"/>
        /// </summary>
        /// <returns></returns>
        public static string IdColumnName
        {
            get { return Config.GetValue<string>("UsersDAL.IdColumnName", "Id"); }
            set { Config.SetValue("UsersDAL.IdColumnName", value); }
        }

        /// <summary>
        /// <add key="ExistsTable" value="SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Users' "/>
        /// </summary>
        /// <returns></returns>
        public static string ExistsTable
        {
            get { return Config.GetValue<string>("ExistsTable", "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Users' "); }
            set { Config.SetValue("UsersDAL.ExistsTable", value); }
        }

        /// <summary>
        /// <add key="CreateTable" value="CREATE TABLE Users (Id bigint IDENTITY(1,1) PRIMARY KEY,UserName	nvarchar(100)	NOT NULL,Email	nvarchar(100)	NOT NULL,Password	nvarchar(20)	NOT NULL,PasswordQuestion	nvarchar(100)	NULL,PasswordAnswer	nvarchar(100)	NULL,Comment	nvarchar(4000)	NULL,IsApproved	bit	NOT NULL,IsLockedOut		bit	NOT NULL,CreationDate	datetime	NOT NULL,LastLoginDate	datetime	NULL,LastActivityDate	datetime	NULL,LastPasswordChangedDate	datetime	NOT NULL,LastLockoutDate	datetime	NOT NULL)"/>
        /// </summary>
        /// <returns></returns>
        public static string CreateTable
        {
            get
            {
                return Config.GetValue<string>("UsersDAL.CreateTable", @"CREATE TABLE Users (Id bigint IDENTITY(1,1) PRIMARY KEY, Username	nvarchar(100)	NOT NULL,Email	nvarchar(100)	NOT NULL,Password	nvarchar(20)	NOT NULL,PasswordQuestion	nvarchar(100)	NULL,PasswordAnswer	nvarchar(100)	NULL,Comment	nvarchar(4000)	NULL,IsApproved	bit	NOT NULL,IsLockedOut		bit	NOT NULL,CreationDate	datetime	NOT NULL,LastLoginDate	datetime	NULL,LastActivityDate	datetime	NULL,LastPasswordChangedDate	datetime	NOT NULL,LastLockoutDate	datetime	NOT NULL)");
            }
            set { Config.SetValue("UsersDAL.CreateTable", value); }
        }

        /// <summary>
        /// <add key="SelectUsersByRoleNameSQL" value="SELECT {0}.* FROM {0} u INNER JOIN UsersRoles ur ON u.Id = ur.IdUser INNER JOIN Roles r ON r.Id = ur.IdRole WHERE r.Name = @0  "/>
        /// </summary>
        /// <returns></returns>
        public static string SelectUsersByRoleNameSQL
        {
            get { return Config.GetValue<string>("UsersDAL.SelectUsersByRoleNameSQL", "SELECT u.* FROM {0} u INNER JOIN UsersRoles ur ON u.Id = ur.UserId INNER JOIN Roles r ON r.Id = ur.RoleId WHERE r.Name = @0  "); }
            set { Config.SetValue("UsersDAL.SelectUsersByRoleNameSQL", value); }
        }

		public static object token = new object();
		private static bool _tableReady = false;
		public static  bool TableReady
		{
			get
			{
				lock (token)
				{
					if (_tableReady == false)
						_tableReady = PrepareTable();
					return _tableReady;
				}
			}
			set { _tableReady = value; }
		}

		private static bool PrepareTable()
		{
			bool tableOk = false;
			try
			{
                object executeScalar = Data.Database.ExecuteScalar<object>(ExistsTable);
                if (executeScalar == null)
                {
                    Log.Warning(string.Format("Table {0} Does not exists", TableName), Log.LogEntry.EntryCategories.DataBase);
                    Data.Database.Execute(CreateTable);
                    Log.Info(string.Format("Table {0} Created", TableName), Log.LogEntry.EntryCategories.DataBase);
                }
                else
                {
                    Log.Debug(string.Format("Table {0} Already Exists", TableName), Log.LogEntry.EntryCategories.DataBase);
                }
				tableOk = true;
			}
			catch (Exception exception)
			{
				Log.Error(exception);
			}
			return tableOk;
		}


		/// <summary>
		/// Gets all the User Records
		/// </summary>
		/// <returns></returns>
		public static List<UserRecord> SelectAll()
		{
			List<UserRecord> list = null;
			if (TableReady)
				list = Data.Database.Fetch<UserRecord>("SELECT * FROM Users");                
			return list;
		}

		/// <summary>
		/// Gets all the User Records
		/// </summary>
		/// <returns></returns>
		public static List<UserRecord> SelectAll(int pageIndex, int pageSize, out int totalRecords)
		{
			List<UserRecord> list = null;
			totalRecords = 0;
			if (TableReady)
			{
				var page = Data.Database.Page<UserRecord>(pageIndex,pageSize,string.Format("SELECT * FROM {0}", TableName));
				list = page.Items;
				totalRecords = (int) page.TotalItems;
			}
			return list;
		}

        /// <summary>
        /// Gets all the User Records related with a Role of a given name
        /// </summary>
        /// <param name="roleName">The name of the role</param>
        /// <returns></returns>
        public static List<UserRecord> SelectUsersByRoleName(string roleName)
        {
            List<UserRecord> list = null;
            if (TableReady && RolesDAL.TableReady && UsersRolesDAL.TableReady)
                list = Data.Database.Fetch<UserRecord>(string.Format(SelectUsersByRoleNameSQL, TableName),roleName);
            return list;
        }

		public static int GetNumberOfUsersOnline(DateTime dateLastActivity)
		{
		    int num =0;
		    if(TableReady)
                num = Data.Database.ExecuteScalar<int>(string.Format("SELECT COUNT(Id) FROM {0} WHERE DateLastActivity > @0", TableName), dateLastActivity);
			return num;
		}

		/// <summary>
		/// Gets a User Record 
		/// </summary>
		/// <param name="id">The User Id</param>
		/// <returns>The User Record </returns>
		public static UserRecord SelectById(long id)
		{
			UserRecord record = null;
			if(TableReady)
				record = Data.Database.SingleOrDefault<UserRecord>(string.Format("SELECT * FROM {0} WHERE Id=@0", TableName), id);
			return record;
		}

		/// <summary>
		/// Gets a User Record 
		/// </summary>
		/// <param name="userName">The User userName</param>
		/// <returns>The User Record </returns>
		public static UserRecord SelectByName(string userName)
		{
		    UserRecord record = null;
		    if(TableReady)
			    record = Data.Database.SingleOrDefault<UserRecord>(string.Format("SELECT * FROM {0} WHERE UserName=@0", TableName), userName);
			return record;
		}

		/// <summary>
		/// Gets a User Record 
		/// </summary>
		/// <param name="email">The User Email</param>
		/// <returns>The User Record </returns>
		public static UserRecord SelectByEmail(string email)
		{
			UserRecord record = null;
			if(TableReady)
				record = Data.Database.SingleOrDefault<UserRecord>(string.Format("SELECT * FROM {0} WHERE Email=@0", TableName), email);
			return record;
		}


		/// <summary>
		/// Inserts a User Record 
		/// </summary>
		/// <param name="record">The User Record</param>
		public static void Insert(UserRecord record)
		{
            if (TableReady)
            {
                if( SelectByEmail(record.Email)!=null || SelectByName(record.Username)!=null)
                    Log.Warning(string.Format("User {0} alredy exists", record.Username));
                else
                    Data.Database.Insert(TableName, IdColumnName, record);
            }
		}

		/// <summary>
		/// Updates a User Record 
		///  </summary>
		/// <param name="record">The User Record and its associated Entity Record</param>
		public static void Update(UserRecord record)
		{
			if (TableReady)
                Data.Database.Update(TableName, IdColumnName, record);
		}

		/// <summary>
		/// Updates a User Record 
		///  </summary>
		/// <param name="record">The User Record and its associated Entity Record</param>
		public static void Delete(UserRecord record)
		{
			if (TableReady)
                Data.Database.Delete(TableName, IdColumnName, record);
		}
	}
}
